<?php

class Menu {

    private $_ID;
    private $_level;
    private $_parentID;
    private $_title;
    private $_link;
    private $_target;
    private $_status;
    private $_order;
    private $_createdDate;
    private $_modifiedDate;
    private $_categoryID;
    private $_createdUserID;
    private $_modifiedUserID;
    private $_menuType;
    private $_createdUser;
    private $_modifiedUser;
    private $_category;

    public function getCategory() {
        return $this->_category;
    }

    public function setCategory($category) {
        $this->_category = $category;
    }

    public function getCategoryID() {
        return $this->_categoryID;
    }

    public function setCategoryID($categoryID) {
        $this->_categoryID = $categoryID;
    }

    public function getCreatedUserID() {
        return $this->_createdUserID;
    }

    public function setCreatedUserID($createdUserID) {
        $this->_createdUserID = $createdUserID;
    }

    public function getModifiedUserID() {
        return $this->_modifiedUserID;
    }

    public function setModifiedUserID($modifiedUserID) {
        $this->_modifiedUserID = $modifiedUserID;
    }

    public function getMenuType() {
        return $this->_menuType;
    }

    public function setMenuType($menuType) {
        $this->_menuType = $menuType;
    }

    public function getID() {
        return $this->_ID;
    }

    public function setID($ID) {
        $this->_ID = $ID;
    }

    public function getLevel() {
        return $this->_level;
    }

    public function setLevel($level) {
        $this->_level = $level;
    }

    public function getParentID() {
        return $this->_parentID;
    }

    public function setParentID($parentID) {
        $this->_parentID = $parentID;
    }

    public function getTitle() {
        return $this->_title;
    }

    public function setTitle($title) {
        $this->_title = $title;
    }

    public function getLink() {
        return $this->_link;
    }

    public function setLink($link) {
        $this->_link = $link;
    }

    public function getTarget() {
        return $this->_target;
    }

    public function setTarget($target) {
        $this->_target = $target;
    }

    public function getStatus() {
        return $this->_status;
    }

    public function setStatus($status) {
        $this->_status = $status;
    }

    public function getOrder() {
        return $this->_order;
    }

    public function setOrder($order) {
        $this->_order = $order;
    }

    public function getCreatedDate() {
        return $this->_createdDate;
    }

    public function setCreatedDate($createdDate) {
        $this->_createdDate = $createdDate;
    }

    public function getModifiedDate() {
        return $this->_modifiedDate;
    }

    public function setModifiedDate($modifiedDate) {
        $this->_modifiedDate = $modifiedDate;
    }

    public function getCreatedUser() {
        return $this->_createdUser;
    }

    public function setCreatedUser($createdUser) {
        $this->_createdUser = $createdUser;
    }

    public function getModifiedUser() {
        return $this->_modifiedUser;
    }

    public function setModifiedUser($modifiedUser) {
        $this->_modifiedUser = $modifiedUser;
    }

    public function add($db = null) {

        if ($db != null) {

            $sql = "INSERT INTO `menu`
                                    (
                                     `M_Level`,
                                     `M_ParentID`,
                                     `M_Title`,
                                     `M_Link`,
                                     `M_Target`,
                                     `M_Status`,
                                     `M_Order`,
                                     `M_CategoryID`,
                                     `M_CreatedDate`,
                                     `M_ModifiedDate`,
                                     `M_CreatedUser`,
                                     `M_ModifiedUser`,
                                     `M_MT_Name`)
                        VALUES (
                                :M_Level,
                                :M_ParentID,
                                :M_Title,
                                :M_Link,
                                :M_Target,
                                :M_Status,
                                :M_Order,
                                :M_CategoryID,
                                :M_CreatedDate,
                                :M_ModifiedDate,
                                :M_CreatedUser,
                                :M_ModifiedUser,
                                :M_MT_Name);";

            $parameters = array(
                ':M_Level', ':M_ParentID',
                ':M_Title', ':M_Link',
                ':M_Target', ':M_Status',
                ':M_Order', ':M_CategoryID',
                ':M_CreatedDate', ':M_ModifiedDate',
                ':M_CreatedUser', ':M_ModifiedUser',
                ':M_MT_Name'
            );
            $values = array(
                $this->getLevel(), $this->getParentID(),
                $this->getTitle(), $this->getLink(),
                $this->getTarget(), $this->getStatus(),
                $this->getOrder(), $this->getCategoryID(),
                $this->getCreatedDate(), $this->getModifiedDate(),
                $this->getCreatedUserID(), $this->getModifiedUserID(),
                $this->getMenuType()
            );
            $types = array(
                PDO::PARAM_INT, PDO::PARAM_INT,
                PDO::PARAM_STR, PDO::PARAM_STR,
                PDO::PARAM_STR, PDO::PARAM_INT,
                PDO::PARAM_INT, PDO::PARAM_INT,
                PDO::PARAM_STR, PDO::PARAM_STR,
                PDO::PARAM_INT, PDO::PARAM_INT,
                PDO::PARAM_STR
            );

            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    public function delete($db = null) {
        if ($db != null) {
            $sql = "DELETE
                    FROM `menu`
                    WHERE `M_ID` = :M_ID;";
            $parameters = array(':M_ID');
            $values = array($this->getID());
            $types = array(PDO::PARAM_INT);
            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    public function update($db = null) {
        if ($db != null) {

            $sql = "UPDATE `menu`
                            SET 
                              `M_Level` = :M_Level,
                              `M_ParentID` = :M_ParentID,
                              `M_Title` = :M_Title,
                              `M_Link` = :M_Link,
                              `M_Target` = :M_Target,
                              `M_Status` = :M_Status,
                              `M_Order` = :M_Order,
                              `M_CategoryID` = :M_CategoryID,
                              `M_ModifiedDate` = :M_ModifiedDate,
                              `M_ModifiedUser` = :M_ModifiedUser,
                              `M_MT_Name` = :M_MT_Name
                            WHERE `M_ID` = :M_ID;";

            $parameters = array(
                ':M_Level', ':M_ParentID',
                ':M_Title', ':M_Link',
                ':M_Target', ':M_Status',
                ':M_Order', ':M_CategoryID',
                ':M_ModifiedDate',
                ':M_ModifiedUser',
                ':M_MT_Name', ':M_ID'
            );

            $values = array(
                $this->getLevel(), $this->getParentID(),
                $this->getTitle(), $this->getLink(),
                $this->getTarget(), $this->getStatus(),
                $this->getOrder(), $this->getCategoryID(),
                $this->getModifiedDate(),
                $this->getModifiedUserID(),
                $this->getMenuType(),
                $this->getID()
            );

            $types = array(
                PDO::PARAM_INT, PDO::PARAM_INT,
                PDO::PARAM_STR, PDO::PARAM_STR,
                PDO::PARAM_STR, PDO::PARAM_INT,
                PDO::PARAM_INT, PDO::PARAM_INT,
                PDO::PARAM_STR,
                PDO::PARAM_INT,
                PDO::PARAM_STR,
                PDO::PARAM_INT
            );

            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    public function updateStatus($db = null) {
        if ($db != null) {
            $sql = "UPDATE `menu`
                    SET `M_Status` = :M_Status
                    WHERE `M_ID` = :M_ID;";
            $parameters = array(':M_Status', ':M_ID');
            $values = array($this->getStatus(), $this->getID());
            $types = array(PDO::PARAM_INT, PDO::PARAM_INT);
            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    public function getListByParentIDAndCategoryID($db = null) {
        if ($db != null) {

            $sql = "SELECT
                    `M_ID`,
                    `M_Level`,
                    `M_ParentID`,
                    `M_Title`,
                    `M_Link`,
                    `M_Target`,
                    `M_Status`,
                    `M_Order`,
                    `M_CategoryID`,
                    `M_CreatedDate`,
                    `M_ModifiedDate`,
                    `M_CreatedUser`,
                    `M_ModifiedUser`,
                    `M_MT_Name`
                  FROM `menu`
                  WHERE M_CategoryID = :M_CategoryID
                  AND M_ParentID = :M_ParentID
                  ORDER BY M_Order ASC";

            $parameters = array(':M_ParentID', ':M_CategoryID');
            $values = array($this->getParentID(), $this->getCategoryID());
            $types = array(PDO::PARAM_INT, PDO::PARAM_INT);
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {
                // mảng danh sách trả về
                $rows = array();

                // lấy mỗi dòng dữ liệu đưa vào mảng danh sách trả về
                while ($row = $result->fetch()) {

                    $menu = new Menu();
                    $menu->setID($row->M_ID);
                    $menu->setParentID($row->M_ParentID);
                    $menu->setLevel($row->M_Level);
                    $menu->setTitle($row->M_Title);
                    $menu->setLink($row->M_Link);
                    $menu->setTarget($row->M_Target);
                    $menu->setStatus($row->M_Status);

                    $menu->setOrder($row->M_Order);
                    $menu->setCategoryID($row->M_CategoryID);

                    $menu->setCreatedUserID($row->M_CreatedUser);
                    $menu->setModifiedUserID($row->M_ModifiedUser);

                    $menu->setCreatedDate($row->M_CreatedDate);
                    $menu->setModifiedDate($row->M_ModifiedDate);
                    $menu->setMenuType($row->M_MT_Name);

                    // created user
                    $createdUser = new Users();
                    $createdUser->setID($menu->getCreatedUserID());
                    $createdUser->getByID($db);
                    $menu->setCreatedUser($createdUser);


                    // modified user
                    $modifiedUser = new Users();
                    $modifiedUser->setID($menu->getModifiedUserID());
                    $modifiedUser->getByID($db);
                    $menu->setModifiedUser($modifiedUser);


                    $rows[] = $menu;
                }
                return $rows;
            } else {
                return null;
            }
        }
    }

    public function getByID($db = null) {
        if ($db != null) {

            $sql = "SELECT
                    `M_ID`,
                    `M_Level`,
                    `M_ParentID`,
                    `M_Title`,
                    `M_Link`,
                    `M_Target`,
                    `M_Status`,
                    `M_Order`,
                    `M_CategoryID`,
                    `M_CreatedDate`,
                    `M_ModifiedDate`,
                    `M_CreatedUser`,
                    `M_ModifiedUser`,
                    `M_MT_Name`
                  FROM `menu`
                  WHERE M_ID = :M_ID
                  ";

            $parameters = array(':M_ID');
            $values = array($this->getID());
            $types = array(PDO::PARAM_INT);
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {
                $row = $result->fetch();
                $this->setID($row->M_ID);
                $this->setParentID($row->M_ParentID);
                $this->setLevel($row->M_Level);
                $this->setTitle($row->M_Title);
                $this->setLink($row->M_Link);
                $this->setTarget($row->M_Target);
                $this->setStatus($row->M_Status);

                $this->setOrder($row->M_Order);
                $this->setCategoryID($row->M_CategoryID);

                $this->setCreatedUserID($row->M_CreatedUser);
                $this->setModifiedUserID($row->M_ModifiedUser);

                $this->setCreatedDate($row->M_CreatedDate);
                $this->setModifiedDate($row->M_ModifiedDate);
                $this->setMenuType($row->M_MT_Name);


                // created user
                $createdUser = new Users();
                $createdUser->setID($this->getCreatedUserID());
                $createdUser->getByID($db);
                $this->setCreatedUser($createdUser);


                // modified user
                $modifiedUser = new Users();
                $modifiedUser->setID($this->getModifiedUserID());
                $modifiedUser->getByID($db);
                $this->setModifiedUser($modifiedUser);


                $category = new Categories();
                $category->setID($this->getCategoryID());
                $category = $category->getByID($db);
                $this->setCategory($category);


                return $this;
            } else {
                return null;
            }
        }
    }

    // hàm lấy thứ tự cao nhất
    public function getMaxOrderByParentID($db) {
        if ($db != null) {
            $sql = "SELECT MAX(M_Order) + 1 AS MaxOrder FROM menu where M_ParentID = :M_ParentID";

            $parameters = array(':M_ParentID');
            $values = array($this->getParentID());
            $types = array(PDO::PARAM_INT);

            $result = $db->executeQuery($sql, $parameters, $values, $types);

            $row = $result->fetch();
            if ($row->MaxOrder != null) {
                return $row->MaxOrder;
            } else {
                return 1;
            }
        }
    }

    public function orderUp($db) {
        if ($db != null) {
            // câu lệnh tải currentOrder, nextOrder, nextArticleID
            $sql = "SELECT (SELECT `M_Order` 
                    FROM menu 
                    WHERE M_ID = :M_ID) AS `currentOrder`,               
                
                    (SELECT `M_Order` FROM menu WHERE M_Level = :M_Level AND `M_Order` <
                    (SELECT `M_Order` FROM menu WHERE M_ID = :M_ID) AND M_ParentID = :M_ParentID
                    ORDER BY `M_Order` DESC LIMIT 1) AS `previousOrder`,

                    (SELECT M_ID FROM menu WHERE M_Level = :M_Level AND `M_Order` = 
                    (SELECT `M_Order` FROM menu WHERE M_Level = :M_Level AND `M_Order` < 
                    (SELECT `M_Order` FROM menu WHERE M_ID = :M_ID) 
                    ORDER BY `M_Order` DESC LIMIT 1) AND M_ParentID = :M_ParentID) AS `previousID`";

            $parameters = array(':M_ID', ':M_Level', ':M_ParentID');
            $values = array($this->getID(), $this->getLevel(), $this->getParentID());
            $types = array(PDO::PARAM_INT, PDO::PARAM_INT, PDO::PARAM_INT);


            $result = $db->executeQuery($sql, $parameters, $values, $types);

            if ($result != null) {
                $row = $result->fetch();
                
                if ($row->previousID != null && $row->previousOrder != null) {
                    // cập nhật order của next item bằng với currentOrder
                    $sqlNextArticle = "update menu set `M_Order` = :M_Order where `M_ID` = :M_ID";
                    $parameters2 = array(':M_Order', ':M_ID');
                    $values2 = array($row->currentOrder, $row->previousID);
                    $types2 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                    $db->executeNonQuery($sqlNextArticle, $parameters2, $values2, $types2);

                    // cập nhật order của current item bằng với next order
                    $sqlCurrentArticle = "update menu set `M_Order` = :M_Order where `M_ID` = :M_ID";
                    $parameters3 = array(':M_Order', ':M_ID');
                    $values3 = array($row->previousOrder, $this->getID());
                    $types3 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                    $db->executeNonQuery($sqlCurrentArticle, $parameters3, $values3, $types3);
                }
            }
        }
    }

    function orderDown($db) {
        if ($db != null) {
            $sql = "SELECT (SELECT `M_Order` 
                    FROM menu 
                    WHERE M_ID = :M_ID) AS `currentOrder`,     
                    
                    (SELECT `M_Order` FROM menu WHERE M_Level = :M_Level AND `M_Order` > 
                    (SELECT `M_Order` FROM menu WHERE M_ID = :M_ID) AND M_ParentID = :M_ParentID
                    ORDER BY `M_Order` ASC LIMIT 1) AS `nextOrder`,

                    (SELECT M_ID FROM menu WHERE M_Level = :M_Level AND `M_Order` = 
                    (SELECT `M_Order` FROM menu WHERE M_Level = :M_Level AND `M_Order` > 
                    (SELECT `M_Order` FROM menu WHERE M_ID = :M_ID) 
                    ORDER BY `M_Order` ASC LIMIT 1) AND M_ParentID = :M_ParentID ) 
                    AS `nextID`";

            $parameters = array(':M_ID', ':M_Level', ':M_ParentID');
            $values = array($this->getID(), $this->getLevel(), $this->getParentID());
            $types = array(PDO::PARAM_INT, PDO::PARAM_INT, PDO::PARAM_INT);

            $result = $db->executeQuery($sql, $parameters, $values, $types);

            if ($result != null) {
                $row = $result->fetch();
                // if exist previousCategoryID and previousOrder
                if ($row->nextID != null && $row->nextOrder != null) {

                    // cập nhật order của previous article bằng với currentOrder
                    $sqlPreviousArticle = "update menu set `M_Order` = :M_Order where `M_ID` = :M_ID";
                    $parameters2 = array(':M_Order', ':M_ID');
                    $values2 = array($row->currentOrder, $row->nextID);
                    $types2 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                    $db->executeNonQuery($sqlPreviousArticle, $parameters2, $values2, $types2);


                    // cập nhật order của current article bằng với previous order
                    $sqlCurrentArticle = "update menu set `M_Order` = :M_Order where `M_ID` = :M_ID";
                    $parameters3 = array(':M_Order', ':M_ID');
                    $values3 = array($row->nextOrder, $this->getID());
                    $types3 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                    $db->executeNonQuery($sqlCurrentArticle, $parameters3, $values3, $types3);
                }
            }
        }
    }

}

?>
